﻿using KonyvKolcsonzes;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Konyvkolcsonzo
{
    struct kolcsonzott
    {
        public long id;
        public long kolcsId;
        public string nev;
        public string cim;
        public string peld;
        public string dat1;
        public string dat2;
    }


    class Kolcsonzes : DAL
    {
        public List<kolcsonzott> getKolcsonzesek(string keresoBetu)
        {
            openConnection();
            string error = "";
            SqlDataReader reader = executeReader("select ID, KikolcsID, Nev, Cim, Peldany, DatKi, DatBe from Kolcsonzes where Nev like '%" + keresoBetu + "%' OR Cim like '%" + keresoBetu + "%'", ref error);
            List<kolcsonzott> nevsor = new List<kolcsonzott>();
            while (reader.Read())
            {
                kolcsonzott k = new kolcsonzott();
                k.id = Convert.ToInt32(reader["ID"]);
                k.kolcsId = Convert.ToInt32(reader["KikolcsID"]);
                k.nev = Convert.ToString(reader["Nev"]);
                k.cim = Convert.ToString(reader["Cim"]);
                k.peld = Convert.ToString(reader["Peldany"]);
                k.dat1 = Convert.ToString(reader["DatKi"]);
                k.dat2 = Convert.ToString(reader["DatBe"]);
                nevsor.Add(k);
            }
            closeConnection();
            return nevsor;
        }

        public List<kolcsonzott> getKolcsonzesKi(string d1, string d2)
        {
            List<kolcsonzott> nevsor = new List<kolcsonzott>();
            SqlDataReader reader;
            SqlConnection conn = new SqlConnection(connectionString);
            using (var cmd = new SqlCommand("PeriodOut", conn)
            {
                CommandType = CommandType.StoredProcedure
            })
            {
                conn.Open();
                cmd.Parameters.Add(new SqlParameter("@dateKi", d1));
                cmd.Parameters.Add(new SqlParameter("@dateBe", d2));
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    kolcsonzott k = new kolcsonzott();
                    k.id = Convert.ToInt32(reader["ID"]);
                    k.kolcsId = Convert.ToInt32(reader["KikolcsID"]);
                    k.nev = Convert.ToString(reader["Nev"]);
                    k.cim = Convert.ToString(reader["Cim"]);
                    k.peld = Convert.ToString(reader["Peldany"]);
                    k.dat1 = Convert.ToString(reader["DatKi"]);
                    k.dat2 = Convert.ToString(reader["DatBe"]);
                    nevsor.Add(k);
                }
                conn.Close();
                return nevsor;
            }
        }

        public List<kolcsonzott> getKolcsonzesBe(string d1, string d2)
        {
            List<kolcsonzott> nevsor = new List<kolcsonzott>();
            SqlDataReader reader;
            SqlConnection conn = new SqlConnection(connectionString);
            using (var cmd = new SqlCommand("PeriodIn", conn)
            {
                CommandType = CommandType.StoredProcedure
            })
            {
                conn.Open();
                cmd.Parameters.Add(new SqlParameter("@dateKi", d1));
                cmd.Parameters.Add(new SqlParameter("@dateBe", d2));
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    kolcsonzott k = new kolcsonzott();
                    k.id = Convert.ToInt32(reader["ID"]);
                    k.kolcsId = Convert.ToInt32(reader["KikolcsID"]);
                    k.nev = Convert.ToString(reader["Nev"]);
                    k.cim = Convert.ToString(reader["Cim"]);
                    k.peld = Convert.ToString(reader["Peldany"]);
                    k.dat1 = Convert.ToString(reader["DatKi"]);
                    k.dat2 = Convert.ToString(reader["DatBe"]);
                    nevsor.Add(k);
                }
                conn.Close();
                return nevsor;
            }
        }


        public string getFelhasznalo()
        {
            SqlDataReader reader;
            SqlConnection conn = new SqlConnection(connectionString);
            using (var cmd = new SqlCommand("GyakoriSzemely", conn)
            {
                CommandType = CommandType.StoredProcedure
            })
            {
                conn.Open();
                string nev = "";
                int nr = new int();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    nr = Convert.ToInt32(reader["Nr"]);
                    nev = Convert.ToString(reader["FelhasznaloNev"]);
                }
                conn.Close();
                return nev;
            }
        }

        public string getKonyv()
        {
            SqlDataReader reader;
            SqlConnection conn = new SqlConnection(connectionString);
            using (var cmd = new SqlCommand("GyakoriKonyv", conn)
            {
                CommandType = CommandType.StoredProcedure
            })
            {
                conn.Open();
                string cim = "";
                int nr = new int();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    nr = Convert.ToInt32(reader["Nr"]);
                    cim = Convert.ToString(reader["Cim"]);
                }
                conn.Close();
                return cim;
            }
        }

    }
}